{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Answers","Psswrds","Sheet1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":30,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Answers","index":0,"n_rows":84,"n_cols":16,"rows_kept":84,"cut":false,"hidden":false,"merged":[[0,1,0,7],[40,12,41,12],[40,13,41,13],[40,11,41,11],[40,9,41,9],[40,10,41,10]]}
[null,"Valuing an Option"]
[]
[null,"As the Christmas season approaches, you feel that consumer sentiment in your"]
[null,"neighborhood is somewhat better than it is reported to be nationally, and you think this"]
[null,"bodes well for regional retail sales. Indeed, you feel that shares in Webmart, a regional"]
[null,"retail chain, are slightly undervalued. Over the next 20 trading days, you believe that the",null,null,null,null,null,null,null,null,null,"`1"]
[null,"share price - currently $66/share - is likely to drift upwards. Specifically, you think that"]
[null,"the share price will, each day, either drop $1 (a 20% chance), stay unchanged (a 45%"]
[null,"chance), or move up by $1 (a 35% chance). (The day-to-day price changes will vary"]
[null,"independently.)"]
[null,null,null,null,null,null,null,null,"change","Pr(change)"]
[null,"What is the expected change in share price in the course of one day?",null,null,null,null,null,null,-1,0.2]
[null,null,null,null,null,null,null,null,0,0.45]
[null,{"$f":"SUMPRODUCT(I12:I14,J12:J14)","v":0.14999999999999997},{"$f":"showformula(B14)","v":"=SUMPRODUCT(I12:I14,J12:J14)"},null,null,null,null,null,1,0.35]
[]
[null,"What is one standard-deviation's-worth of uncertainty in the one-day price change?"]
[]
[null,{"$f":"SQRT(SUMPRODUCT(I12:I14^2,J12:J14)-B14^2)","v":0.72629195231669752},{"$f":"showformula(B18)","v":"=SQRT(SUMPRODUCT(I12:I14^2,J12:J14)-B14^2)"}]
[]
[null,"What is the expected stock price 20 days from now?"]
[]
[null,{"$f":"66+20*B14","v":69},{"$f":"showformula(B22)","v":"=66+20*B14"}]
[]
[null,"What is one standard-deviation's-worth of uncertainty in the stock price 20 days from"]
[null,"now?"]
[]
[null,{"$f":"SQRT(20)*B18","v":3.2480763537823432},{"$f":"showformula(B27)","v":"=SQRT(20)*B18"}]
[]
[null,"You don't have enough free capital to buy 100 shares in Webmart right now. However,"]
[null,"the November $70 call option, which expires on the third Friday in November - exactly"]
[null,"20 trading days from now - is selling for only a $0.50 premium."]
[]
[null,"(This means that, for $50, you can purchase an option to buy 100 shares in Webmart at"]
[null,"a price of $70 on the expiration day. On the day of expiration, if the share price is below"]
[null,"$70, your option will be valueless (and you'll lose your $50). However, if the share price",null,null,null,null,null,null,null,null,null,null,null,null,null,"cumulative"]
[null,"is above $70, you'll be able to exercise the option, and immediately sell the 100 shares",null,null,null,null,null,null,null,null,null,null,null,"change","Pr(change)",0]
[null,"at the market price. For example, if the share price is $73 twenty days from now, the",null,null,null,null,null,null,null,null,null,null,null,-1,0.2,{"$f":"P36+O37","v":0.2}]
[null,"option will net you 100⋅($73-$70) - $50 = $250 in profit. To keep things simple, we'll",null,null,null,null,null,null,null,null,null,null,null,0,0.45,{"$f":"P37+O38","v":0.65}]
[null,"ignore commissions and taxes.)",null,null,null,null,null,null,null,null,null,null,null,1,0.35,{"$f":"P38+O39","v":1}]
[]
[null,"Using 50,000 simulation runs, estimate your expected profit from the purchase of a",null,null,null,null,null,null,null,"share price","=RAND","price change","another way","another way"]
[null,"100-share November $70 call option."]
[null,null,null,null,null,null,null,null,"start",66,{"$f":"RAND()","v":0.28405770438385392},{"$f":"IF(K43<0.2,-1,IF(K43<0.65,0,1))","v":0},{"$f":"LOOKUP(RAND(),$P$36:$P$38,$N$37:$N$39)","v":-1},{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0},{"$f":"showformula(L43)","v":"=IF(K43<0.2,-1,IF(K43<0.65,0,1))"}]
[null,null,null,{"$f":"-50+IF(J63>70,100*(J63-70),0)","v":350},{"$f":"showformula(D44)","v":"=-50+IF(J63>70,100*(J63-70),0)"},null,null,null,"1 day later",{"$f":"J43+L43","v":66},0.93897099225765224,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0},{"$f":"showformula(M43)","v":"=LOOKUP(RAND(),$P$36:$P$38,$N$37:$N$39)"}]
[null,null,null,null,"profit from buying option",null,null,null,"2 days later",67,0.55384335576764865,0,1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0},{"$f":"showformula(N43)","v":"=LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})"}]
[null,null,null,null,null,null,null,null,"3 days later",67,0.33507685212926119,0,-1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":-1}]
[null,null,{"$f":"ADDRESS(ROW($J$63),COLUMN($J$63))","v":"$J$63"},{"$f":"ADDRESS(ROW($D$44),COLUMN($D$44))","v":"$D$44"},"monitored cell",null,null,null,"4 days later",67,0.31737913986458244,0,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":1}]
[null,null,69.027439999999999,35.665999999999997,"mean",null,null,null,"5 days later",67,0.33072165445361779,0,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":-1}]
[null,null,3.2494766083731959,155.16706474107065,"sample standard deviation",null,null,null,"6 days later",67,0.75278524146415815,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":-1}]
[null,null,55,-50,"minimum",null,null,null,"7 days later",68,0.36983368084327728,0,1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":1}]
[null,null,82,1150,"maximum",null,null,null,"8 days later",68,0.90982374174063241,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0}]
[null,null,50000,50000,"number of simulation runs",null,null,null,"9 days later",69,0.17462577372860388,-1,1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0}]
[null,null,{"$f":"1.96*C49/SQRT(C52)","v":2.8482918303462279E-2},{"$f":"1.96*D49/SQRT(D52)","v":1.3600992901501676},"margin of error (95% confidence)",null,null,null,"10 days later",68,0.73559126013731402,1,-1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":1}]
[null,null,null,null,null,null,null,null,"11 days later",69,0.28975919414989948,0,-1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":1}]
[null,"These are the precise values, derived (painfully) on the next page:",null,null,null,null,null,null,"12 days later",69,0.91153253546546487,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0}]
[null,null,null,null,null,null,null,null,"13 days later",70,0.76240490154727014,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0}]
[null,null,null,34.62074608033344,"E[profit from buying option]",null,null,null,"14 days later",71,0.28756443650577268,0,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":-1}]
[null,null,null,153.27592434168244,"StDev(profit)",null,null,null,"15 days later",71,0.7342854301455719,1,1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":-1}]
[null,null,null,0.32513582730897383,"Pr(option pays off)",null,null,null,"16 days later",72,0.90200050182399205,1,-1,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":-1}]
[null,null,null,null,null,null,null,null,"17 days later",73,0.78487305502425431,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":0}]
[null,null,null,null,null,null,null,null,"18 days later",74,8.9367680373457326E-2,-1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":1}]
[null,null,null,null,null,null,null,null,"19 days later",73,0.95838738791844746,1,0,{"$f":"LOOKUP(RAND(),{0,0.2,0.65;-1,0,1})","v":1}]
[null,null,null,null,null,null,null,null,"20 days later",74]
[]
[]
[]
[null,"Don't worry about this below-the-line material. It's only here to illustrate some advanced (non-required) material."]
[]
[]
[null,"Here's an estimate of the probability that the option pays off,"]
[null,"made using the Central Limit Theorem:"]
[]
[null,null,null,{"$f":"1-NORMDIST(70.5,B22,B27,TRUE)","v":0.32210814268187915},"Pr(option pays off)",null,null,null,{"$f":"showformula(D73)","v":"=1-NORMDIST(70.5,B22,B27,TRUE)"}]
[]
[null,"And here's an estimate of the value of the option:"]
[]
[null,null,null,{"$f":"B22+B27^2*NORMDIST(70.5,B22,B27,FALSE)/(1-NORMDIST(70.5,B22,B27,TRUE))","v":72.615959595475317},"E[closing price | closes above $70]",null,null,null,{"$f":"showformula(D77)","v":"=B22+B27^2*NORMDIST(70.5,B22,B27,FALSE)/(1-NORMDIST(70.5,B22,B27,TRUE))"}]
[null,null,null,{"$f":"-50+(D77-70)*100*D73","v":34.262188662939437},"E[profit from buying option]",null,null,null,{"$f":"showformula(D78)","v":"=-50+(D77-70)*100*D73"}]
[]
[null,null,null,null,null,null,null,null,"This formula was copied from the Week-5 workbook \"Adverse_selection_plus.xls.\""]
[]
[null,null,null,null,null,null,null,null,"Since only full-dollar amounts were possible in the problem, and the option is valueless"]
[null,null,null,null,null,null,null,null,"at closing prices of $70 or less while having positive value at closing prices of $71 or"]
[null,null,null,null,null,null,null,null,"more, I set my \"dividing line\" in the normal approximation at $70.5."]

{"sheet":"Psswrds","index":1,"n_rows":98,"n_cols":7,"rows_kept":98,"cut":false,"hidden":true,"merged":[]}
["NetID","Password","Offset","Index",null,"NetID","Password"]
["aak301","m&g7221",1,1,null,{"$f":"LOWER(#REF!)","v":{"$error":"#REF!"}},{"$f":"LOWER(#REF!)","v":{"$error":"#REF!"}}]
["act732","yahoo",2,2]
["aka876","moscow80",3,3,null,{"$f":"LOOKUP(F2,A:A,D:D)","v":{"$error":"#REF!"}},{"$f":"showformula(F4)","v":"=LOOKUP(F2,A:A,D:D)"}]
["ald556","sammi",4,4,null,{"$f":"IF(AND(OFFSET(A1,F4,0)=F2,OFFSET(B1,F4,0)=G2),1,0)","v":{"$error":"#REF!"}},{"$f":"showformula(F5)","v":"=IF(AND(OFFSET(A1,F4,0)=F2,OFFSET(B1,F4,0)=G2),1,0)"}]
["ama122","brasil",5,5,null,{"$f":"IF(ISNA(F4),0,F5)","v":{"$error":"#REF!"}},{"$f":"showformula(F6)","v":"=IF(ISNA(F4),0,F5)"}]
["amg559","gins27",6,6,null,{"$f":"OFFSET(A1,location,2)","v":{"$error":"#REF!"}},{"$f":"showformula(F7)","v":"=OFFSET(A1,location,2)"}]
["apa128","ampamp",7,7]
["asc739","drewski",8,8]
["aso733","dankss",9,9]
["asu551","qwerty",10,10]
["awi807","codered",11,11]
["bcn538","bubba1",12,12]
["bkh179","sasi1arc",13,13]
["bku272","dragon",14,14]
["bms394","critt24er",15,15]
["bsi656","molde44",16,16]
["cbe387","salsa",17,17]
["cme270","plymouth",18,18]
["cms077","bubbawoo",19,19]
["dar580","jonna",20,20]
["dda737","travica",21,21]
["dmu577","perfect36",22,22]
["dpp522","minerva",23,23]
["drs160","tewligan",24,24]
["dtk674","joe22sph",25,25]
["eer846","tiffany",26,26]
["gch518","panda",27,27]
["jap367","cabernet",28,28]
["jas731","23ryan",29,29]
["jay306","mystic1k",30,30]
["jbr582","cosmo2",31,31]
["jch308","1234",32,32]
["jda422","d3jdwcs",33,33]
["jdk714","tarheels",34,34]
["jdl593","jdl409",35,35]
["jem203","hallie",36,36]
["jgo328","barlow1122",37,37]
["jhi824","kclass",38,38]
["jjs743","cervelo",39,39]
["jlc521","longhorns",40,40]
["jle769","focuss",41,41]
["jlo188","snoggy",42,42]
["jmf513","jodes",43,43]
["jmi138","315315",44,44]
["jpa331","bluegiraffe",45,45]
["jps207","notredame",46,46]
["jro292","fall08a",47,47]
["jtg300","football",48,48]
["jva798","panful",49,49]
["kaz706","oxford",50,50]
["kbg568","ilovestats",51,51]
["klf832","addison",52,52]
["kmp719","goose",53,53]
["koc205","donkey1",54,54]
["kpf717","cubs2008",55,55]
["kpo366","clare4me",56,56]
["lbk579","statsrule",57,57]
["lbw733","brooke",58,58]
["lhb298","random",59,59]
["lhb578","maine1",60,60]
["lmh853","lhamilt3",61,61]
["mac197","markcanty",62,62]
["mbj299","stephanie",63,63]
["mcm305","michael",64,64]
["mdg121","singapore",65,65]
["met124","cherry",66,66]
["mjw368","decs",67,67]
["mpa529","patilmh",68,68]
["mpa724","chocolate",69,69]
["mpa885","myhero",70,70]
["nke725","kellogg",71,71]
["npa722","gocubsgo",72,72]
["pah273","madmax",73,73]
["pjs114","klavieres",74,74]
["pvc530","magic11",75,75]
["rka222","@nai:shu",76,76]
["rmb830","ryan5683",77,77]
["rms925","lasagna",78,78]
["rtb831","ava2008",79,79]
["sba974","sas@sas2",80,80]
["sch715","password1",81,81]
["shc595","wisconsin",82,82]
["sja746","boris",83,83]
["sjs594","sjs71579",84,84]
["slw279","tompetty",85,85]
["sma724","lyons66",86,86]
["smb287","joshua",87,87]
["smg767","ufgator1",88,88]
["spb265","breyer",89,89]
["sth695","sant5357",90,90]
["tjl932","ace44",91,91]
["tjs591","whistler",92,92]
["tlh926","calendula",93,93]
["tmf442","alphazeta",94,94]
["tmf635","b7lu7e",95,95]
["wjo838","b1illy",96,96]
["wpc235","football",97,97]

{"sheet":"Sheet1","index":2,"n_rows":10,"n_cols":16,"rows_kept":10,"cut":false,"hidden":true,"merged":[]}
[]
[null,"total",null,"#1","#2","#3","#4","#5","#6","#7","#8","#9","#10","#11","#12"]
[null,{"$f":"SUM(C9:O9)","v":51},null,45,36,51,51,44,50,42,49,36,24,0,0]
[null,null,null,{"$f":"D3/$B$3","v":0.88235294117647056},0.70588235294117652,1,1,0.86274509803921573,0.98039215686274506,0.82352941176470584,0.96078431372549022,0.70588235294117652,0.47058823529411764,0,0]
[]
[]
[null,"absent","0 correct","1 correct","2 correct","3 correct","4 correct","5 correct","6 correct","7 correct","8 correct","9 correct","10 correct","11 correct","12 correct"]
[]
[null,3,0,0,0,1,1,0,6,4,9,15,15,0,0]
[null,{"$f":"B9/(B9+B3)","v":5.5555555555555552E-2},{"$f":"C9/$B$3","v":0},0,0,1.9607843137254902E-2,1.9607843137254902E-2,0,0.11764705882352941,7.8431372549019607E-2,0.17647058823529413,0.29411764705882354,0.29411764705882354,0,0,{"$f":"SUM(C10:O10)","v":1}]